#!/usr/bin/perl -w
use strict;
use DBI;
use Email::Valid;
use Net::SMTP;
sub sendEmail
{
my ($to, $from, $subject, $message) = @_;
my $sendmail = '/usr/sbin/sendmail';
open(MAIL, "|$sendmail -oi -t");
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$message\n";
close(MAIL);
}
my @times=localtime();
my $time_mins = $times[1] + $times[2] * 60;
my $time_day = $times[6];
my $db = "tar";
my $dbh = DBI->connect('DBI:mysql:tar','fbads','fbads') or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare("select user_id,email_id,areas from user_prefs where time_of_day = ? and days_of_week like '%$time_day%' order by time_of_day;")  or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->bind_param( 1, $time_mins);
$sth->execute();
my ($user_id,$email_id, $areas);
$sth->bind_columns(\$user_id,\$email_id,\$areas);
while( $sth->fetch() ) {
        #print "$user_id, $areas, $email_id\n";
        unless(Email::Valid->address($email_id)) {
	next;
	}
        $areas =~ s/,/','/g;
        $areas = '\''.$areas.'\'';
        my $sth2 = $dbh->prepare("select area,area_code, current_status from status where area_code in ($areas)")  or die "Couldn't prepare statement: " . $dbh->errstr;
        $sth2->execute();
        my($area,$area_code, $current_status);
        $sth2->bind_columns(\$area,\$area_code, \$current_status);
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
	my $message = "Traffic alert at $hour:$min on $mday/$mon/".(1900+$year)."\n";
        while( $sth2->fetch() ) {
                $message.= "$area -> $current_status\n";
        }
	sendEmail($email_id,"yashman\@gmail.com","Traffic Alert",$message);
}
$sth->finish();
$dbh->disconnect();
